package com.vs.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.vs.model.ExpenseForm;
import com.vs.model.ExpenseSearchForm;

public class ExpensesOperation {

	private String expenseInsertString = "insert into expenses(expense_type_id,project_id,expense_date,expense_amt,notes,pay_mode,chq_number,chq_date,bank_name,chq_comment,supplier_name,added_by) values(?,?,?,?,?,?,?,?,?,?,?,?)";

	private String expenseDeleteString = "delete from expenses where expense_id=?";

	// private String getExpenseString =
	// "select expense_id,expense_type_id,expense_date,expense_amt,notes from expenses";
	// private String searchString =
	// "select expense_id,e.project_id,p.project_name,e.expense_type_id,expense_name,expense_date,expense_amt,notes from  expenses e join expense_type et on e.expense_type_id=et.expense_type_id join projects p on e.project_id=p.project_id where e.project_id = ?";
	//private String searchString = "call get_expense_list(?,?,?,?,?,?)";
	
	private String searchString = "call get_epense_list_new(?,?,?,?,?,?)";
	
	private String getExpenseString = "call get_expense_details(?)";
	private String editExpenseString = "update expenses set expense_type_id=?,expense_date=?,expense_amt=?,notes=?,"
			+ "pay_mode=?,chq_number=?,chq_date=?,bank_name=?,chq_comment=?,"
			+ "supplier_name=?,modified_by=? where  expense_id=? ";

	//

	public ExpenseForm getExpense(Integer expenseId) {
		System.out.println("getExpense called for expenseId=" + expenseId);
		Connection conn = null;
		ExpenseForm expenseType = null;
		try {
			conn = new DBConnection().getConnection();
			if (conn == null) {
				System.out.println("conn not found");
			} else {
				// System.out.println("conn found");
			}
			PreparedStatement ps = conn.prepareStatement(getExpenseString);
			ps.setInt(1, expenseId);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				expenseType = new ExpenseForm();
				expenseType.setExpenseId(rs.getInt("expense_id"));
				expenseType.setExpenseProjectId(rs.getInt("project_id"));
				expenseType.setExpenseProjectName(rs.getString("project_name"));
				expenseType.setExpenseTypeId(rs.getInt("expense_type_id"));
				expenseType.setExpenseTypeName(rs.getString("expense_name"));
				expenseType.setLaborTypeId(rs.getInt("labor_type_id"));
				expenseType.setExpenseDate(rs.getString("expense_date"));
				expenseType.setExpenseAmount(rs.getString("expense_amt"));
				expenseType.setExpenseSupplierName(rs.getString("supplier_name"));
				expenseType.setNotes(rs.getString("notes"));
				expenseType.setPayMode(rs.getString("pay_mode"));
				expenseType.setChqNo(rs.getString("chq_number"));
				expenseType.setChqDate(rs.getString("chq_date"));
				expenseType.setBankName(rs.getString("bank_name"));
				expenseType.setChqComment(rs.getString("chq_comment"));
			}
			rs.close();
			ps.close();
			conn.commit();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return expenseType;

	}

	public boolean deleteExpense(Integer expenseId) {
		System.out.println("deleteExpense called for expenseId=" + expenseId);
		Connection conn = null;
		boolean success = false;
		try {
			conn = new DBConnection().getConnection();
			if (conn == null) {
				System.out.println("conn not found");
			} else {
				// System.out.println("conn found");
			}
			PreparedStatement ps = conn.prepareStatement(expenseDeleteString);
			ps.setInt(1, expenseId);
			ps.execute();
			ps.close();
			conn.commit();
			success = true;

		} catch (Exception e) {
			e.printStackTrace();
			success = false;
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return success;

	}

	public boolean addExpense(ExpenseForm expenseForm) {
		Connection conn = null;
		boolean success = false;
		try {
			conn = new DBConnection().getConnection();
			if (conn == null) {
				System.out.println("conn not found");
			} else {
				// System.out.println("conn found");
			}
			PreparedStatement psExpenseInsert = conn
					.prepareStatement(expenseInsertString);
			psExpenseInsert.setInt(1, expenseForm.getExpenseTypeId());
			psExpenseInsert.setInt(2, expenseForm.getExpenseProjectId());
			psExpenseInsert.setString(3, expenseForm.getExpenseDate());
			psExpenseInsert.setString(4, expenseForm.getExpenseAmount());
			psExpenseInsert.setString(5, expenseForm.getNotes());
			psExpenseInsert.setString(6, expenseForm.getPayMode());
			psExpenseInsert.setString(7, expenseForm.getChqNo());
			psExpenseInsert.setString(8, expenseForm.getChqDate());
			psExpenseInsert.setString(9, expenseForm.getBankName());
			psExpenseInsert.setString(10, expenseForm.getChqComment());
			psExpenseInsert.setString(11, expenseForm.getExpenseSupplierName());
			psExpenseInsert.setString(12, expenseForm.getAddedBy());
			psExpenseInsert.execute();
			psExpenseInsert.close();
			conn.commit();
			success = true;

		} catch (Exception e) {
			e.printStackTrace();
			success = false;
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return success;
	}

	public boolean editExpense(ExpenseForm expenseForm) {
		Connection conn = null;
		boolean success = false;
		try {
			conn = new DBConnection().getConnection();
			if (conn == null) {
				System.out.println("conn not found");
			} else {
				// System.out.println("conn found");
			}
			
			PreparedStatement psExpenseInsert = conn
					.prepareStatement(editExpenseString);
			psExpenseInsert.setInt(1, expenseForm.getExpenseTypeId());
			psExpenseInsert.setString(2, expenseForm.getExpenseDate());
			psExpenseInsert.setString(3, expenseForm.getExpenseAmount());
			psExpenseInsert.setString(4, expenseForm.getNotes());
			psExpenseInsert.setString(5, expenseForm.getPayMode());
			psExpenseInsert.setString(6, expenseForm.getChqNo());
			psExpenseInsert.setString(7, expenseForm.getChqDate());
			psExpenseInsert.setString(8, expenseForm.getBankName());
			psExpenseInsert.setString(9, expenseForm.getChqComment());
			psExpenseInsert.setString(10, expenseForm.getExpenseSupplierName());
			psExpenseInsert.setString(11, expenseForm.getModifiedBy());
			psExpenseInsert.setInt(12, expenseForm.getExpenseId());
			psExpenseInsert.execute();
			psExpenseInsert.close();
			conn.commit();
			success = true;

		} catch (Exception e) {
			e.printStackTrace();
			success = false;
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return success;
	}

	public List<ExpenseForm> getExpensesList(ExpenseSearchForm expenseSearchForm) {
		Connection conn = null;
		List<ExpenseForm> expenseList = new ArrayList<ExpenseForm>();
		System.out.println("inside getExpensesList");
		System.out.println(expenseSearchForm);
		try {
			conn = new DBConnection().getConnection();
			if (conn == null) {
				System.out.println("conn not found");
			} else {
				// System.out.println("conn found");
			}
			
			if(expenseSearchForm.getExpenseProjectId()==null)
			{
				expenseSearchForm.setExpenseProjectId(0);
			}
			if(expenseSearchForm.getExpenseTypeId()==null)
			{
				expenseSearchForm.setExpenseTypeId(0);
			}
			if(expenseSearchForm.getLaborTypeId()==null)
			{
				expenseSearchForm.setLaborTypeId(0);
			}
			
			PreparedStatement ps = conn.prepareStatement(searchString);
			ps.setInt(1, expenseSearchForm.getExpenseProjectId());
			ps.setInt(2, expenseSearchForm.getExpenseTypeId());
			ps.setInt(3, expenseSearchForm.getLaborTypeId());
			ps.setString(4, expenseSearchForm.getExpenseAmount());
			ps.setString(5, expenseSearchForm.getExpenseFromDate());
			ps.setString(6, expenseSearchForm.getExpenseToDate());
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				ExpenseForm expenseType = new ExpenseForm();
				expenseType.setExpenseId(rs.getInt("expense_id"));
				expenseType.setExpenseProjectId(rs.getInt("project_id"));
				expenseType.setExpenseProjectName(rs.getString("project_name"));
				expenseType.setExpenseTypeId(rs.getInt("expense_type_id"));
				expenseType.setLaborTypeId(rs.getInt("labor_type_id"));
				expenseType.setExpenseTypeName(rs.getString("expense_name"));
				expenseType.setExpenseDate(rs.getString("expense_date"));
				expenseType.setExpenseAmount(rs.getString("expense_amt"));
				expenseType.setNotes(rs.getString("notes"));
				expenseType.setPayMode(rs.getString("pay_mode"));
				expenseType.setChqNo(rs.getString("chq_number"));
				expenseType.setChqDate(rs.getString("chq_date"));
				expenseType.setBankName(rs.getString("bank_name"));
				expenseType.setChqComment(rs.getString("chq_comment"));
				expenseList.add(expenseType);
			}
			ps.close();
			conn.commit();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		System.out.println("expenseList size:" + expenseList.size());
		return expenseList;
	}

	public static void main(String args[]) {
		// List<ExpenseForm> expenseList = new ExpensesOperation()
		// .getExpensesList();
		// System.out.println("size:" + expenseList.size());

	}
}
